package com.hexacta.hrs.web.struts.actions.reports;

import java.awt.Color;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Comparator;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.Transformer;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.vectrics.location.Location;
import org.vectrics.user.EnglishLevelType;
import org.vectrics.user.ProgrammignLanguageType;

import com.hexacta.hrs.report.EmployeeReportSummary;
import com.hexacta.hrs.report.Tuple;
import com.hexacta.hrs.services.employee.EmployeeService;
import com.hexacta.hrs.web.struts.actions.BaseReportAction;

import edu.emory.mathcs.backport.java.util.Collections;

public class EmployeeReportsSummaryAction extends BaseReportAction {

    private static final long serialVersionUID = 1L;

    private static String EXCEL = "excel";

    private EmployeeService employeeService;

    private Date selectedStartDate;

    private Date selectedEndDate;

    private Date initialStartDate;

    private Date initialEndDate;

    private InputStream excelStream;

    private String fileName;

    @Override
    public String execute() throws Exception {

        if (getSelectedStartDate() == null) {
            setSelectedStartDate(getInitialStartDate());
        }
        if (getSelectedEndDate() == null) {
            setSelectedEndDate(getInitialEndDate());
        }
        return super.execute();
    }

    public List<EmployeeReportSummary> getEmployeesByOffice() {
        List<Object> resultQuery = employeeService.getEmployeesByOffice();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value2 = ((Long) employeeResume[1]).toString();
                return new EmployeeReportSummary(column, "Cantidad", value2);
            }

        };

        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });

        return employeeReports;
    }

    public String getEmployeesByOfficeTotal() {
        List<EmployeeReportSummary> employeeReports = getEmployeesByOffice();
        Integer total = 0;

        for (EmployeeReportSummary employeeReportSummary : employeeReports) {
            total += Integer.valueOf(employeeReportSummary.getTotal());
        }
        return total.toString();
    }

    public List<EmployeeReportSummary> getEmployeesByGender() {
        List<Object> resultQuery = employeeService.getEmployeesByGender();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("m")) {
                    value1 = "Hombres";
                } else {
                    value1 = "Mujeres";
                }
                return new EmployeeReportSummary(value1, column, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getTuple().get(0).getValue1().compareToIgnoreCase(o2.getTuple().get(0).getValue1());
            }

        });
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                iterator.remove();
            }

        }
        if (employeeReports.get(0).getTuple().size() != employeeReports.get(1).getTuple().size()) {
            int j = 0;
            if (employeeReports.get(0).getTuple().size() < employeeReports.get(1).getTuple().size()) {
                j = 1;
            }
            for (int i = 0; i < employeeReports.get(j).getTuple().size(); i++) {
                String value1 = employeeReports.get(0).getTuple().get(i).getValue1();
                String value2 = employeeReports.get(1).getTuple().get(i).getValue1();
                if (!value1.equalsIgnoreCase(value2)) {
                    if (value1.compareToIgnoreCase(value2) < 0) {
                        employeeReports.get(1).getTuple().add(i, new Tuple(value1, "0"));
                    } else if (value1.compareToIgnoreCase(value2) > 0) {
                        employeeReports.get(0).getTuple().add(i, new Tuple(value1, "0"));
                    }
                }

            }
        }

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesByGenderColumn() {
        List<Object> resultQuery = employeeService.getEmployeesByGender();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("m")) {
                    value1 = "Hombres";
                } else {
                    value1 = "Mujeres";
                }
                return new EmployeeReportSummary(column, value1, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                iterator.remove();
            }

        }

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesMarried() {
        List<Object> resultQuery = employeeService.getEmployeesMarried();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("m")) {
                    value1 = "Hombres";
                } else {
                    value1 = "Mujeres";
                }
                return new EmployeeReportSummary(value1, column, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getTuple().get(0).getValue1().compareToIgnoreCase(o2.getTuple().get(0).getValue1());
            }

        });
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                iterator.remove();
            }

        }
        if (employeeReports.get(0).getTuple().size() != employeeReports.get(1).getTuple().size()) {
            int j = 0;
            if (employeeReports.get(0).getTuple().size() < employeeReports.get(1).getTuple().size()) {
                j = 1;
            }
            for (int i = 0; i < employeeReports.get(j).getTuple().size(); i++) {
                String value1 = employeeReports.get(0).getTuple().get(i).getValue1();
                String value2 = employeeReports.get(1).getTuple().get(i).getValue1();
                if (!value1.equalsIgnoreCase(value2)) {
                    if (value1.compareToIgnoreCase(value2) < 0) {
                        employeeReports.get(1).getTuple().add(i, new Tuple(value1, "0"));
                    } else if (value1.compareToIgnoreCase(value2) > 0) {
                        employeeReports.get(0).getTuple().add(i, new Tuple(value1, "0"));
                    }
                }

            }
        }

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesMarriedColumn() {
        List<Object> resultQuery = employeeService.getEmployeesWithChildren();
        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("m")) {
                    value1 = "Hombres";
                } else {
                    value1 = "Mujeres";
                }
                return new EmployeeReportSummary(column, value1, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                iterator.remove();
            }

        }

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesWithChildren() {
        List<Object> resultQuery = employeeService.getEmployeesWithChildren();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("m")) {
                    value1 = "Hombres";
                } else {
                    value1 = "Mujeres";
                }
                return new EmployeeReportSummary(value1, column, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getTuple().get(0).getValue1().compareToIgnoreCase(o2.getTuple().get(0).getValue1());
            }

        });
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                iterator.remove();
            }

        }
        if (employeeReports.get(0).getTuple().size() != employeeReports.get(1).getTuple().size()) {
            int j = 0;
            if (employeeReports.get(0).getTuple().size() < employeeReports.get(1).getTuple().size()) {
                j = 1;
            }
            for (int i = 0; i < employeeReports.get(j).getTuple().size(); i++) {
                String value1 = employeeReports.get(0).getTuple().get(i).getValue1();
                String value2 = employeeReports.get(1).getTuple().get(i).getValue1();
                if (!value1.equalsIgnoreCase(value2)) {
                    if (value1.compareToIgnoreCase(value2) < 0) {
                        employeeReports.get(1).getTuple().add(i, new Tuple(value1, "0"));
                    } else if (value1.compareToIgnoreCase(value2) > 0) {
                        employeeReports.get(0).getTuple().add(i, new Tuple(value1, "0"));
                    }
                }

            }
        }

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesWithChildrenColumn() {
        List<Object> resultQuery = employeeService.getEmployeesWithChildren();
        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("m")) {
                    value1 = "Hombres";
                } else {
                    value1 = "Mujeres";
                }
                return new EmployeeReportSummary(column, value1, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                iterator.remove();
            }

        }

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesByJourney() {
        List<Object> resultQuery = employeeService.getEmployeesByJourney();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = ((Double) employeeResume[1]).toString();
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("8.0")) {
                    value1 = "Completa";
                } else {
                    value1 = "Parcial";
                }
                return new EmployeeReportSummary(value1, column, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getTuple().get(0).getValue1().compareToIgnoreCase(o2.getTuple().get(0).getValue1());
            }

        });
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;
        String aux = "";
        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();
            Long cant = null;
            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
                aux = employeeReportSummary.getTuple().get(0).getValue1();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                if (aux.equalsIgnoreCase(employeeReportSummary.getTuple().get(0).getValue1())) {
                    cant = Long.valueOf(employeeAux.getTuple().get(employeeAux.getTuple().size() - 1).getValue2()
                            .get(0));
                    cant += Long.valueOf(tuple.getValue2().get(0));
                    employeeAux.getTuple().get(employeeAux.getTuple().size() - 1).getValue2().set(0, cant.toString());
                } else {
                    employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                }
                aux = employeeReportSummary.getTuple().get(0).getValue1();
                iterator.remove();
            }

        }

        if (employeeReports.get(0).getTuple().size() != employeeReports.get(1).getTuple().size()) {
            int j = 0;
            if (employeeReports.get(0).getTuple().size() < employeeReports.get(1).getTuple().size()) {
                j = 1;
            }
            for (int i = 0; i < employeeReports.get(j).getTuple().size(); i++) {
                String value1 = employeeReports.get(0).getTuple().get(i).getValue1();
                String value2 = employeeReports.get(1).getTuple().get(i).getValue1();
                if (!value1.equalsIgnoreCase(value2)) {
                    if (value1.compareToIgnoreCase(value2) < 0) {
                        employeeReports.get(1).getTuple().add(i, new Tuple(value1, "0"));
                    } else if (value1.compareToIgnoreCase(value2) > 0) {
                        employeeReports.get(0).getTuple().add(i, new Tuple(value1, "0"));
                    }
                }

            }
        }

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesByJourneyColumn() {
        List<Object> resultQuery = employeeService.getEmployeesByJourney();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = ((Double) employeeResume[1]).toString();
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("8.0")) {
                    value1 = "Completa";
                } else {
                    value1 = "Parcial";
                }
                return new EmployeeReportSummary(column, value1, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getTuple().get(0).getValue1().compareToIgnoreCase(o2.getTuple().get(0).getValue1());
            }

        });
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;
        String aux = "";
        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();
            Long cant = null;
            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
                aux = employeeReportSummary.getTuple().get(0).getValue1();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                if (aux.equalsIgnoreCase(employeeReportSummary.getTuple().get(0).getValue1())) {
                    cant = Long.valueOf(employeeAux.getTuple().get(employeeAux.getTuple().size() - 1).getValue2()
                            .get(0));
                    cant += Long.valueOf(tuple.getValue2().get(0));
                    employeeAux.getTuple().get(employeeAux.getTuple().size() - 1).getValue2().set(0, cant.toString());
                } else {
                    employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                }
                aux = employeeReportSummary.getTuple().get(0).getValue1();
                iterator.remove();
            }

        }
        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesByDegree() {
        List<Object> resultQuery = employeeService.getEmployeesByDegree();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("f")) {
                    value1 = "Completo";
                } else {
                    value1 = "Incompleto";
                }
                return new EmployeeReportSummary(value1, column, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getTuple().get(0).getValue1().compareToIgnoreCase(o2.getTuple().get(0).getValue1());
            }

        });
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;
        String aux = "";
        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();
            Long cant = null;
            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
                aux = employeeReportSummary.getTuple().get(0).getValue1();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                if (aux.equalsIgnoreCase(employeeReportSummary.getTuple().get(0).getValue1())) {
                    cant = Long.valueOf(employeeAux.getTuple().get(employeeAux.getTuple().size() - 1).getValue2()
                            .get(0));
                    cant += Long.valueOf(tuple.getValue2().get(0));
                    employeeAux.getTuple().get(employeeAux.getTuple().size() - 1).getValue2().set(0, cant.toString());
                } else {
                    employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                }
                aux = employeeReportSummary.getTuple().get(0).getValue1();
                iterator.remove();
            }

        }

        if (employeeReports.get(0).getTuple().size() != employeeReports.get(1).getTuple().size()) {
            int j = 0;
            if (employeeReports.get(0).getTuple().size() < employeeReports.get(1).getTuple().size()) {
                j = 1;
            }
            for (int i = 0; i < employeeReports.get(j).getTuple().size(); i++) {
                String value1 = employeeReports.get(0).getTuple().get(i).getValue1();
                String value2 = employeeReports.get(1).getTuple().get(i).getValue1();
                if (!value1.equalsIgnoreCase(value2)) {
                    if (value1.compareToIgnoreCase(value2) < 0) {
                        employeeReports.get(1).getTuple().add(i, new Tuple(value1, "0"));
                    } else if (value1.compareToIgnoreCase(value2) > 0) {
                        employeeReports.get(0).getTuple().add(i, new Tuple(value1, "0"));
                    }
                }

            }
        }

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesByDegreeColumn() {
        List<Object> resultQuery = employeeService.getEmployeesByDegree();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Location) employeeResume[0]).getName();
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                if (value1.equalsIgnoreCase("f")) {
                    value1 = "Completo";
                } else {
                    value1 = "Incompleto";
                }
                return new EmployeeReportSummary(column, value1, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getTuple().get(0).getValue1().compareToIgnoreCase(o2.getTuple().get(0).getValue1());
            }

        });
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;
        String aux = "";
        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();
            Long cant = null;
            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
                aux = employeeReportSummary.getTuple().get(0).getValue1();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                if (aux.equalsIgnoreCase(employeeReportSummary.getTuple().get(0).getValue1())) {
                    cant = Long.valueOf(employeeAux.getTuple().get(employeeAux.getTuple().size() - 1).getValue2()
                            .get(0));
                    cant += Long.valueOf(tuple.getValue2().get(0));
                    employeeAux.getTuple().get(employeeAux.getTuple().size() - 1).getValue2().set(0, cant.toString());
                } else {
                    employeeAux.addTuple(tuple.getValue1(), tuple.getValue2().get(0));
                }
                aux = employeeReportSummary.getTuple().get(0).getValue1();
                iterator.remove();
            }

        }
        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesBySeniorityAndArea() {
        List<Object> resultQuery = employeeService.getEmployeesBySeniorityAndArea();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = (String) employeeResume[0];
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();

                return new EmployeeReportSummary(column, value1, value2);
            }
        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);

        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {
            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getTuple().get(0).getValue1().compareToIgnoreCase(o2.getTuple().get(0).getValue1());
            }
        });
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {
            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }
        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();

                List<Tuple> tuple = new ArrayList<Tuple>();
                Tuple tupleAux = employeeReportSummary.getTuple().get(0);
                tuple.add(tupleAux);
                employeeAux.setTuple(tuple);

            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);

                if (employeeReportSummary.getColumn().equalsIgnoreCase(employeeAux.getColumn())) {
                    employeeAux.getTuple().add(tuple);
                }
                iterator.remove();
            }

        }
        List<EmployeeReportSummary> listEmployeeReportsSummaryColumns = getEmployeesBySeniorityAndAreaColumn();
        for (EmployeeReportSummary employeeReportSummary : employeeReports) {
            for (int i = 0; i < listEmployeeReportsSummaryColumns.size(); i++) {
                EmployeeReportSummary employeeReportSummaryAux = listEmployeeReportsSummaryColumns.get(i);
                try {
                    Tuple tuple = employeeReportSummary.getTuple().get(i);
                    if (!employeeReportSummaryAux.getColumn().equalsIgnoreCase(tuple.getValue1())) {
                        employeeReportSummary.getTuple().add(i, new Tuple(employeeReportSummaryAux.getColumn(), "0"));
                    }
                } catch (Exception e) {
                    employeeReportSummary.getTuple().add(i, new Tuple(employeeReportSummaryAux.getColumn(), "0"));
                }

            }
        }
        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesBySeniorityAndAreaColumn() {
        List<Object> resultQuery = employeeService.getEmployeesBySeniorityAndArea();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = (String) employeeResume[0];
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                return new EmployeeReportSummary(value1, column, value2);
            }
        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }
        });
        Iterator<EmployeeReportSummary> iterator;
        String gender = "";

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(gender)) {
                gender = employeeReportSummary.getColumn();
            } else {
                iterator.remove();
            }
        }
        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesBySeniorityAndGender() {
        List<Object> resultQuery = employeeService.getEmployeesBySeniorityAndGender();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = (String) employeeResume[0];
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                if (column.equalsIgnoreCase("m")) {
                    column = "Hombres";
                } else {
                    column = "Mujeres";
                }
                return new EmployeeReportSummary(column, value1, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String gender = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(gender)) {
                employeeAux = employeeReportSummary;
                gender = employeeReportSummary.getColumn();

                List<Tuple> tuple = new ArrayList<Tuple>();
                Tuple tupleAux = employeeReportSummary.getTuple().get(0);
                tuple.add(tupleAux);
                employeeAux.setTuple(tuple);

            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);

                if (employeeReportSummary.getColumn().equalsIgnoreCase(employeeAux.getColumn())) {
                    employeeAux.getTuple().add(tuple);
                }

                iterator.remove();

            }
        }
        EmployeeReportSummary employeeSummaryToCompare = employeeReports.get(1);
        for (int i = 0; i < employeeReports.get(0).getTuple().size(); i++) {
            Tuple tuple = employeeReports.get(0).getTuple().get(i);
            Tuple tupleToCompare = employeeSummaryToCompare.getTuple().get(i);

            if (tuple.getValue1().compareToIgnoreCase(tupleToCompare.getValue1()) > 0) {
                Tuple tupleAux = new Tuple(tupleToCompare.getValue1(), "0");
                employeeReports.get(0).getTuple().add(i, tupleAux);
            } else if (tuple.getValue1().compareToIgnoreCase(tupleToCompare.getValue1()) < 0) {
                Tuple tupleAux = new Tuple(tuple.getValue1(), "0");
                employeeSummaryToCompare.getTuple().add(i, tupleAux);
            }
        }

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesBySeniorityAndGenderColumn() {
        List<Object> resultQuery = employeeService.getEmployeesBySeniorityAndGender();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = (String) employeeResume[0];
                String value1 = (String) employeeResume[1];
                String value2 = ((Long) employeeResume[2]).toString();
                return new EmployeeReportSummary(value1, column, value2);
            }

        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);
        Collections.sort(employeeReports, new Comparator<EmployeeReportSummary>() {

            @Override
            public int compare(final EmployeeReportSummary o1, final EmployeeReportSummary o2) {

                return o1.getColumn().compareToIgnoreCase(o2.getColumn());
            }

        });
        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();

                List<Tuple> tuple = new ArrayList<Tuple>();
                Tuple tupleAux = employeeReportSummary.getTuple().get(0);
                tuple.add(tupleAux);
                employeeAux.setTuple(tuple);

            } else {
                iterator.remove();
            }
        }
        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesByLanguage() {
        List<Object> resultQuery = employeeService.getEmployeesByLanguage();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((ProgrammignLanguageType) employeeResume[0]).toString();
                String value2 = ((Long) employeeResume[1]).toString();
                return new EmployeeReportSummary(column, "Cantidad", value2);
            }

        };

        CollectionUtils.collect(resultQuery, transformer, employeeReports);

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesByEnglishLevel() {
        List<Object> resultQuery = employeeService.getEmployeesByEnglishLevel();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((EnglishLevelType) employeeResume[0]).toString();
                String value2 = ((Long) employeeResume[1]).toString();
                return new EmployeeReportSummary(column, "Cantidad", value2);
            }

        };

        CollectionUtils.collect(resultQuery, transformer, employeeReports);

        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesByAntiquity() {
        List<Object> resultQuery = employeeService.getEmployeesByAntiquity();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Integer) employeeResume[0]).toString();
                String value2 = ((Long) employeeResume[1]).toString();
                if (Integer.valueOf(column) <= 1) {
                    column = "0 a 1";
                } else if (Integer.valueOf(column) <= 3) {
                    column = "1 a 3";
                } else if (Integer.valueOf(column) <= 5) {
                    column = "3 a 5";
                } else if (Integer.valueOf(column) <= 10) {
                    column = "5 a 10";
                } else if (Integer.valueOf(column) > 10) {
                    column = "mas de 10";
                }
                return new EmployeeReportSummary(column, "Cantidad", value2);
            }

        };

        CollectionUtils.collect(resultQuery, transformer, employeeReports);

        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();
            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                Long cant = Long.valueOf(employeeAux.getTuple().get(0).getValue2().get(0));
                cant += Long.valueOf(tuple.getValue2().get(0));
                employeeAux.getTuple().get(0).getValue2().set(0, cant.toString());

                iterator.remove();
            }
        }
        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesByAge() {
        List<Object> resultQuery = employeeService.getEmployeesByAge();

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();

        Transformer transformer = new Transformer() {
            @Override
            public Object transform(final Object input) {
                Object[] employeeResume = (Object[]) input;
                String column = ((Integer) employeeResume[0]).toString();
                String value2 = ((Long) employeeResume[1]).toString();
                if (Integer.valueOf(column) < 25) {
                    column = "<25";
                } else if (Integer.valueOf(column) <= 34) {
                    column = "25 a 35";
                } else if (Integer.valueOf(column) <= 45) {
                    column = "35 a 45";
                } else if (Integer.valueOf(column) > 45) {
                    column = ">45";
                }
                return new EmployeeReportSummary(column, "Cantidad", value2);
            }
        };
        CollectionUtils.collect(resultQuery, transformer, employeeReports);

        Iterator<EmployeeReportSummary> iterator;
        String column = "";
        EmployeeReportSummary employeeAux = null;

        for (iterator = employeeReports.iterator(); iterator.hasNext();) {
            EmployeeReportSummary employeeReportSummary = iterator.next();

            if (!employeeReportSummary.getColumn().equalsIgnoreCase(column)) {
                employeeAux = employeeReportSummary;
                column = employeeReportSummary.getColumn();
            } else {
                Tuple tuple = employeeReportSummary.getTuple().get(0);
                Long cant = Long.valueOf(employeeAux.getTuple().get(0).getValue2().get(0));
                cant += Long.valueOf(tuple.getValue2().get(0));
                employeeAux.getTuple().get(0).getValue2().set(0, cant.toString());

                iterator.remove();
            }
        }
        return employeeReports;
    }

    public List<EmployeeReportSummary> getEmployeesFlow() {
        Calendar begin = Calendar.getInstance();
        Calendar end = Calendar.getInstance();
        begin.setTime(selectedStartDate);
        end.setTime(selectedEndDate);

        String ingresos = employeeService.getEnteringEmployees(begin, end);
        String egresos = employeeService.getExitingEmployees(begin, end);

        List<EmployeeReportSummary> employeeReports = new ArrayList<EmployeeReportSummary>();
        employeeReports.add(new EmployeeReportSummary("Ingresos y Bajas", ingresos, egresos));

        return employeeReports;
    }

    /**
     * Exportaci�n a Excel
     * 
     * @return
     * @throws IOException
     * @throws Exception
     */
    public String excelStream() throws IOException, Exception {
        XSSFWorkbook wb = new XSSFWorkbook();

        Sheet sheet = wb.createSheet("Employees Summary Report");

        sheet.setColumnWidth(0, 2 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        for (int i = 2; i < 50; i++) {
            sheet.setColumnWidth(i, 18 * 256);
        }

        Integer currentRow = 0;

        Integer currentCell = 1;

        Row rowOffice = sheet.createRow(currentRow + 1);

        Row rowData = sheet.createRow(currentRow + 2);
        Cell cellTittle;
        Cell cellRowsTittle;

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getTittlesStyle(wb));
        for (int i = 2; i < 6; i++) {
            cellTittle = rowOffice.createCell(i);
            cellTittle.setCellStyle(getTittlesStyle(wb));
        }
        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getTittlesStyle(wb));
        cellTittle.setCellValue("Manage Employee Reports");
        sheet.addMergedRegion(new CellRangeAddress(currentRow + 1, currentRow + 1, 1, 5));

        currentRow += 2;
        // Ingresos y bajas

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Flujo  de Personal");

        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");

        cellTittle = rowOffice.createCell(currentCell + 1);
        cellTittle.setCellStyle(getTittlesStyle(wb));
        cellTittle.setCellValue("Inicio: " + sdf.format(getSelectedStartDate()));
        cellTittle = rowOffice.createCell(currentCell + 2);
        cellTittle.setCellStyle(getTittlesStyle(wb));
        cellTittle.setCellValue("Fin: " + sdf.format(getSelectedEndDate()));

        for (EmployeeReportSummary employeeByOffice : getEmployeesFlow()) {
            for (Tuple tuple : employeeByOffice.getTuple()) {
                cellRowsTittle = rowData.createCell(1);
                cellRowsTittle.setCellStyle(getTittlesStyle(wb));
                cellRowsTittle.setCellValue("Ingresos");

                Cell cellData = rowData.createCell(2);
                cellData.setCellStyle(getDataStyle(wb));
                cellData.setCellValue(Integer.valueOf(tuple.getValue1()));
                cellData = rowData.createCell(3);
                cellData.setCellStyle(getDataStyle(wb));
                sheet.addMergedRegion(new CellRangeAddress(currentRow + 2, currentRow + 2, 2, 3));
                for (String value : tuple.getValue2()) {
                    rowData = sheet.createRow(currentRow + 3);
                    cellRowsTittle = rowData.createCell(1);
                    cellRowsTittle.setCellStyle(getTittlesStyle(wb));
                    cellRowsTittle.setCellValue("Bajas");

                    cellData = rowData.createCell(2);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    cellData = rowData.createCell(3);
                    cellData.setCellStyle(getDataStyle(wb));

                    sheet.addMergedRegion(new CellRangeAddress(currentRow + 3, currentRow + 3, 2, 3));
                }
            }

        }

        currentRow += 5;
        currentCell = 1;

        // Gente por oficina
        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Gente por oficina");
        cellRowsTittle = rowData.createCell(currentCell);
        cellRowsTittle.setCellStyle(getTittlesStyle(wb));
        cellRowsTittle.setCellValue("Cantidad");

        Integer total = 0;
        for (EmployeeReportSummary employeeByOffice : getEmployeesByOffice()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    total += Integer.valueOf(value);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
        }
        Cell cellTotalTittle = rowOffice.createCell(currentCell + 1);
        cellTotalTittle.setCellStyle(getTittlesStyle(wb));
        cellTotalTittle.setCellValue("Total");
        Cell cellTotalValue = rowData.createCell(currentCell + 1);
        cellTotalValue.setCellStyle(getHighlightStyle(wb));
        cellTotalValue.setCellValue(total);

        currentRow += 4;
        currentCell = 1;
        total = 0;

        // Hombres y Mujeres

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Hombres y Mujeres");
        cellRowsTittle = rowData.createCell(currentCell);

        for (EmployeeReportSummary employeeByOffice : getEmployeesByGenderColumn()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            currentCell++;
        }
        cellTotalTittle = rowOffice.createCell(currentCell + 1);
        cellTotalTittle.setCellStyle(getTittlesStyle(wb));
        cellTotalTittle.setCellValue("Total");

        currentRow += 2;
        currentCell = 1;

        for (EmployeeReportSummary employeeByOffice : getEmployeesByGender()) {
            cellRowsTittle = rowData.createCell(1);
            cellRowsTittle.setCellStyle(getTittlesStyle(wb));
            cellRowsTittle.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    total += Integer.valueOf(value);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
            cellTotalValue = rowData.createCell(currentCell + 1);
            cellTotalValue.setCellStyle(getHighlightStyle(wb));
            cellTotalValue.setCellValue(total);
            total = 0;
            rowData = sheet.createRow(currentRow + 1);
            currentRow++;
            currentCell = 1;
        }
        currentRow += 1;
        currentCell = 1;
        total = 0;

        // Casados

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Casados");
        cellRowsTittle = rowData.createCell(currentCell);

        for (EmployeeReportSummary employeeByOffice : getEmployeesMarriedColumn()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            currentCell++;
        }
        cellTotalTittle = rowOffice.createCell(currentCell + 1);
        cellTotalTittle.setCellStyle(getTittlesStyle(wb));
        cellTotalTittle.setCellValue("Total");

        currentRow += 2;
        currentCell = 1;

        for (EmployeeReportSummary employeeByOffice : getEmployeesMarried()) {
            cellRowsTittle = rowData.createCell(1);
            cellRowsTittle.setCellStyle(getTittlesStyle(wb));
            cellRowsTittle.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    total += Integer.valueOf(value);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
            cellTotalValue = rowData.createCell(currentCell + 1);
            cellTotalValue.setCellStyle(getHighlightStyle(wb));
            cellTotalValue.setCellValue(total);
            total = 0;
            rowData = sheet.createRow(currentRow + 1);
            currentRow++;
            currentCell = 1;
        }
        currentRow += 1;
        currentCell = 1;
        total = 0;

        // Gente con hijos

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Gente con hijos");
        cellRowsTittle = rowData.createCell(currentCell);

        for (EmployeeReportSummary employeeByOffice : getEmployeesWithChildrenColumn()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            currentCell++;
        }
        cellTotalTittle = rowOffice.createCell(currentCell + 1);
        cellTotalTittle.setCellStyle(getTittlesStyle(wb));
        cellTotalTittle.setCellValue("Total");

        currentRow += 2;
        currentCell = 1;

        for (EmployeeReportSummary employeeByOffice : getEmployeesWithChildren()) {
            cellRowsTittle = rowData.createCell(1);
            cellRowsTittle.setCellStyle(getTittlesStyle(wb));
            cellRowsTittle.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    total += Integer.valueOf(value);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
            cellTotalValue = rowData.createCell(currentCell + 1);
            cellTotalValue.setCellStyle(getHighlightStyle(wb));
            cellTotalValue.setCellValue(total);
            total = 0;
            rowData = sheet.createRow(currentRow + 1);
            currentRow++;
            currentCell = 1;
        }
        currentRow += 1;
        currentCell = 1;
        total = 0;

        // Jornada

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Jornada");
        cellRowsTittle = rowData.createCell(currentCell);

        for (EmployeeReportSummary employeeByOffice : getEmployeesByJourneyColumn()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            currentCell++;
        }
        cellTotalTittle = rowOffice.createCell(currentCell + 1);
        cellTotalTittle.setCellStyle(getTittlesStyle(wb));
        cellTotalTittle.setCellValue("Total");

        currentRow += 2;
        currentCell = 1;

        for (EmployeeReportSummary employeeByOffice : getEmployeesByJourney()) {
            cellRowsTittle = rowData.createCell(1);
            cellRowsTittle.setCellStyle(getTittlesStyle(wb));
            cellRowsTittle.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    total += Integer.valueOf(value);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
            cellTotalValue = rowData.createCell(currentCell + 1);
            cellTotalValue.setCellStyle(getHighlightStyle(wb));
            cellTotalValue.setCellValue(total);
            total = 0;
            rowData = sheet.createRow(currentRow + 1);
            currentRow++;
            currentCell = 1;
        }
        currentRow += 1;
        currentCell = 1;
        total = 0;
        // Universitarios

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Universitarios");
        cellRowsTittle = rowData.createCell(currentCell);

        for (EmployeeReportSummary employeeByOffice : getEmployeesByDegreeColumn()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            currentCell++;
        }
        cellTotalTittle = rowOffice.createCell(currentCell + 1);
        cellTotalTittle.setCellStyle(getTittlesStyle(wb));
        cellTotalTittle.setCellValue("Total");

        currentRow += 2;
        currentCell = 1;

        for (EmployeeReportSummary employeeByOffice : getEmployeesByDegree()) {
            cellRowsTittle = rowData.createCell(1);
            cellRowsTittle.setCellStyle(getTittlesStyle(wb));
            cellRowsTittle.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    total += Integer.valueOf(value);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
            cellTotalValue = rowData.createCell(currentCell + 1);
            cellTotalValue.setCellStyle(getHighlightStyle(wb));
            cellTotalValue.setCellValue(total);
            total = 0;
            rowData = sheet.createRow(currentRow + 1);
            currentRow++;
            currentCell = 1;
        }
        currentRow += 1;
        currentCell = 1;
        total = 0;

        // Se�ority por Area

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Se�ority por Area");
        cellRowsTittle = rowData.createCell(currentCell);

        for (EmployeeReportSummary employeeByOffice : getEmployeesBySeniorityAndAreaColumn()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            currentCell++;
        }
        // cellTotalTittle = rowOffice.createCell(currentCell+1);
        // cellTotalTittle.setCellStyle(getTittlesStyle(wb));
        // cellTotalTittle.setCellValue("Total");

        currentRow += 2;
        currentCell = 1;

        for (EmployeeReportSummary employeeByOffice : getEmployeesBySeniorityAndArea()) {
            cellRowsTittle = rowData.createCell(1);
            cellRowsTittle.setCellStyle(getTittlesStyle(wb));
            cellRowsTittle.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    // total += Integer.valueOf(value);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
            // cellTotalValue = rowData.createCell(currentCell+1);
            // cellTotalValue.setCellStyle(getHighlightStyle(wb));
            // cellTotalValue.setCellValue(total);
            // total = 0;
            rowData = sheet.createRow(currentRow + 1);
            currentRow++;
            currentCell = 1;
        }
        currentRow += 1;
        currentCell = 1;
        total = 0;
        // Se�ority por G�nero

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Se�ority por G�nero");
        cellRowsTittle = rowData.createCell(currentCell);

        for (EmployeeReportSummary employeeByOffice : getEmployeesBySeniorityAndGenderColumn()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            currentCell++;
        }
        // cellTotalTittle = rowOffice.createCell(currentCell+1);
        // cellTotalTittle.setCellStyle(getTittlesStyle(wb));
        // cellTotalTittle.setCellValue("Total");

        currentRow += 2;
        currentCell = 1;

        for (EmployeeReportSummary employeeByOffice : getEmployeesBySeniorityAndGender()) {
            cellRowsTittle = rowData.createCell(1);
            cellRowsTittle.setCellStyle(getTittlesStyle(wb));
            cellRowsTittle.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    // total += Integer.valueOf(value);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
            // cellTotalValue = rowData.createCell(currentCell+1);
            // cellTotalValue.setCellStyle(getHighlightStyle(wb));
            // cellTotalValue.setCellValue(total);
            // total = 0;
            rowData = sheet.createRow(currentRow + 1);
            currentRow++;
            currentCell = 1;
        }
        currentRow += 1;
        currentCell = 1;
        total = 0;

        // Lenguaje

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Lenguaje");
        cellRowsTittle = rowData.createCell(currentCell);
        cellRowsTittle.setCellStyle(getTittlesStyle(wb));
        cellRowsTittle.setCellValue("Cantidad");

        for (EmployeeReportSummary employeeByOffice : getEmployeesByLanguage()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
        }

        currentRow += 4;
        currentCell = 1;

        // Nivel de Ingl�s

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Nivel de ingl�s");
        cellRowsTittle = rowData.createCell(currentCell);
        cellRowsTittle.setCellStyle(getTittlesStyle(wb));
        cellRowsTittle.setCellValue("Cantidad");

        for (EmployeeReportSummary employeeByOffice : getEmployeesByEnglishLevel()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
        }

        currentRow += 4;
        currentCell = 1;

        // Antiguedad

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Antig�edad (a�os)");
        cellRowsTittle = rowData.createCell(currentCell);
        cellRowsTittle.setCellStyle(getTittlesStyle(wb));
        cellRowsTittle.setCellValue("Cantidad");

        for (EmployeeReportSummary employeeByOffice : getEmployeesByAntiquity()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
        }

        currentRow += 4;
        currentCell = 1;

        // Edad

        rowOffice = sheet.createRow(currentRow + 1);
        rowData = sheet.createRow(currentRow + 2);

        cellTittle = rowOffice.createCell(currentCell);
        cellTittle.setCellStyle(getHighlightStyle(wb));
        cellTittle.setCellValue("Edad (a�os)");
        cellRowsTittle = rowData.createCell(currentCell);
        cellRowsTittle.setCellStyle(getTittlesStyle(wb));
        cellRowsTittle.setCellValue("Cantidad");

        for (EmployeeReportSummary employeeByOffice : getEmployeesByAge()) {
            Cell cellHeader = rowOffice.createCell(currentCell + 1);
            cellHeader.setCellStyle(getTittlesStyle(wb));
            cellHeader.setCellValue(employeeByOffice.getColumn());
            for (Tuple tuple : employeeByOffice.getTuple()) {
                for (String value : tuple.getValue2()) {
                    Cell cellData = rowData.createCell(currentCell + 1);
                    cellData.setCellStyle(getDataStyle(wb));
                    cellData.setCellValue(Integer.valueOf(value));
                    currentCell++;
                }
            }
        }

        currentRow += 4;
        currentCell = 1;

        // Grabado del archivo
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        excelStream = new ByteArrayInputStream(baos.toByteArray());
        fileName = "Employees.xlsx";

        baos.close();

        return EXCEL;
    }

    public XSSFCellStyle getTittlesStyle(final XSSFWorkbook wb) {
        XSSFCellStyle style = wb.createCellStyle();
        style.setBorderTop((short) 1);
        style.setBorderBottom((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);
        style.setFillForegroundColor(new XSSFColor(new Color(215, 228, 188)));
        style.setFillPattern(style.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);

        XSSFFont font = wb.createFont();
        font.setFontName("Calibri");
        font.setBold(true);
        font.setItalic(true);
        style.setFont(font);
        return style;
    }

    public XSSFCellStyle getHighlightStyle(final XSSFWorkbook wb) {
        XSSFCellStyle style = wb.createCellStyle();
        style.setBorderTop((short) 1);
        style.setBorderBottom((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);
        style.setFillForegroundColor(new XSSFColor(new Color(147, 205, 221)));
        style.setFillPattern(style.SOLID_FOREGROUND);
        // style.setAlignment(HorizontalAlignment.CENTER);

        XSSFFont font = wb.createFont();
        font.setFontName("Calibri");
        font.setBold(true);
        font.setItalic(true);
        style.setFont(font);
        return style;
    }

    public XSSFCellStyle getDataStyle(final XSSFWorkbook wb) {
        XSSFCellStyle style = wb.createCellStyle();
        style.setBorderTop((short) 1);
        style.setBorderBottom((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);
        style.setFillForegroundColor(new XSSFColor(new Color(219, 229, 241)));
        style.setFillPattern(style.SOLID_FOREGROUND);
        // style.setAlignment(HorizontalAlignment.CENTER);

        XSSFFont font = wb.createFont();
        font.setFontName("Calibri");
        font.setBold(false);
        font.setItalic(false);
        style.setFont(font);
        return style;
    }

    public EmployeeService getEmployeeService() {
        return employeeService;
    }

    public void setEmployeeService(final EmployeeService employeeService) {
        this.employeeService = employeeService;
    }

    public Date getSelectedStartDate() {
        return selectedStartDate;
    }

    public void setSelectedStartDate(final Date selectedStartDate) {
        this.selectedStartDate = selectedStartDate;
    }

    public Date getSelectedEndDate() {
        return selectedEndDate;
    }

    public void setSelectedEndDate(final Date selectedEndDate) {
        this.selectedEndDate = selectedEndDate;
    }

    public Date getInitialStartDate() {
        Calendar initialDay = Calendar.getInstance();
        initialDay.set(Calendar.DAY_OF_YEAR, Calendar.getInstance().getMinimum(Calendar.DAY_OF_YEAR));
        return initialDay.getTime();
    }

    public void setInitialStartDate(final Date initialStartDate) {
        this.initialStartDate = initialStartDate;
    }

    public Date getInitialEndDate() {
        Calendar initialDay = Calendar.getInstance();
        initialDay.set(Calendar.DAY_OF_YEAR, Calendar.getInstance().getMaximum(Calendar.DAY_OF_YEAR));
        return initialDay.getTime();
    }

    public void setInitialEndDate(final Date initialdEndDate) {
        initialEndDate = initialdEndDate;
    }

    public String getContentDisposition() {
        return "attachment;filename= " + fileName;
    }

    public InputStream getExcelStream() {
        return excelStream;
    }

    public void setExcelStream(final InputStream excelStream) {
        this.excelStream = excelStream;
    }

}
